using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Text;

namespace Lzfy_Refund_Service.Repositories
{
    public class PermissionRepository : IPermissionRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public PermissionRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<List<MenuDto>> GetMenuTreeAsync(MenuTreeRequest request)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();
            
            if (!string.IsNullOrEmpty(request.MenuName))
            {
                whereConditions.Add("menu_name LIKE @MenuName");
                parameters.Add("@MenuName", $"%{request.MenuName}%");
            }
            
            if (request.IsActive.HasValue)
            {
                whereConditions.Add("is_active = @IsActive");
                parameters.Add("@IsActive", request.IsActive.Value);
            }
            
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            
            var sql = $@"
                SELECT id as Id, parent_id as ParentId, menu_name as MenuName, 
                       menu_code as MenuCode, menu_type as MenuType, 
                       path as Path, icon as Icon, 
                       sort_order as SortOrder, is_active as IsActive, 
                       description as Description
                FROM sys_menus 
                {whereClause}
                ORDER BY sort_order, id";
            
            var menus = await connection.QueryAsync<MenuDto>(sql, parameters);
            return menus.ToList();
        }

        public async Task<List<MenuDto>> GetAllMenusAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT id as Id, parent_id as ParentId, menu_name as MenuName, 
                       menu_code as MenuCode, menu_type as MenuType, 
                       path as Path, icon as Icon, 
                       sort_order as SortOrder, is_active as IsActive, 
                       description as Description
                FROM sys_menus 
                WHERE is_active = 1
                ORDER BY sort_order, id";
            
            var menus = await connection.QueryAsync<MenuDto>(sql);
            return menus.ToList();
        }

        public async Task<Menu?> GetMenuByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT id as Id, parent_id as ParentId, menu_name as MenuName, 
                       menu_code as MenuCode, menu_type as MenuType, 
                       path as Path, icon as Icon, 
                       sort_order as SortOrder, is_active as IsActive, 
                       description as Description
                FROM sys_menus 
                WHERE id = @Id";
            
            return await connection.QuerySingleOrDefaultAsync<Menu>(sql, new { Id = id });
        }

        public async Task<int> AddMenuAsync(Menu menu)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                INSERT INTO sys_menus (parent_id, menu_name, menu_code, menu_type, 
                                     menu_url, menu_icon, sort_order, is_active, description)
                VALUES (@ParentId, @MenuName, @MenuCode, @MenuType, 
                        @Path, @Icon, @SortOrder, @IsActive, @Description);
                SELECT CAST(SCOPE_IDENTITY() as int);";
            
            return await connection.QuerySingleAsync<int>(sql, menu);
        }

        public async Task<bool> UpdateMenuAsync(Menu menu)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                UPDATE sys_menus 
                SET parent_id = @ParentId, menu_name = @MenuName, 
                    menu_code = @MenuCode, menu_type = @MenuType, 
                    path = @Path, icon = @Icon, 
                    sort_order = @SortOrder, is_active = @IsActive, 
                    description = @Description
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, menu);
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteMenuAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            // 检查是否有子菜单
            var childCountSql = "SELECT COUNT(*) FROM sys_menus WHERE parent_id = @Id";
            var childCount = await connection.QuerySingleAsync<int>(childCountSql, new { Id = id });
            
            if (childCount > 0)
            {
                throw new InvalidOperationException("该菜单下还有子菜单，无法删除");
            }
            
            // 检查是否有角色关联此菜单
            var roleMenuCountSql = "SELECT COUNT(*) FROM sys_role_menus WHERE menu_id = @Id";
            var roleMenuCount = await connection.QuerySingleAsync<int>(roleMenuCountSql, new { Id = id });
            
            if (roleMenuCount > 0)
            {
                throw new InvalidOperationException("该菜单已分配给角色，无法删除");
            }
            
            var sql = "DELETE FROM sys_menus WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }

        public async Task<bool> ToggleMenuStatusAsync(int id, bool isActive)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "UPDATE sys_menus SET is_active = @IsActive WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id, IsActive = isActive });
            return rowsAffected > 0;
        }

        public async Task<List<MenuDto>> GetRoleMenusAsync(int roleId)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT m.id as Id, m.parent_id as ParentId, m.menu_name as MenuName, 
                       m.menu_code as MenuCode, m.menu_type as MenuType, 
                       m.path as Path, m.icon as Icon, 
                       m.sort_order as SortOrder, m.is_active as IsActive, 
                       m.description as Description
                FROM sys_menus m
                INNER JOIN sys_role_menus rm ON m.id = rm.menu_id
                WHERE rm.role_id = @RoleId AND m.is_active = 1
                ORDER BY m.sort_order, m.id";
            
            var menus = await connection.QueryAsync<MenuDto>(sql, new { RoleId = roleId });
            return menus.ToList();
        }

        public async Task<bool> AssignRolePermissionsAsync(int roleId, List<int> menuIds)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var transaction = connection.BeginTransaction();
            
            try
            {
                // 先删除现有权限
                await connection.ExecuteAsync(
                    "DELETE FROM sys_role_menus WHERE role_id = @RoleId", 
                    new { RoleId = roleId }, transaction);
                
                // 添加新权限
                if (menuIds.Any())
                {
                    var insertSql = "INSERT INTO sys_role_menus (role_id, menu_id) VALUES (@RoleId, @MenuId)";
                    foreach (var menuId in menuIds)
                    {
                        await connection.ExecuteAsync(insertSql, 
                            new { RoleId = roleId, MenuId = menuId }, transaction);
                    }
                }
                
                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                return false;
            }
        }

        public async Task<bool> RemoveRolePermissionsAsync(int roleId)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "DELETE FROM sys_role_menus WHERE role_id = @RoleId";
            var rowsAffected = await connection.ExecuteAsync(sql, new { RoleId = roleId });
            return rowsAffected >= 0; // 即使没有权限也算成功
        }

        public async Task<List<MenuDto>> GetUserMenusAsync(int userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT DISTINCT m.id as Id, m.parent_id as ParentId, m.menu_name as MenuName, 
                       m.menu_code as MenuCode, m.menu_type as MenuType, 
                       m.path as Path, m.icon as Icon, 
                       m.sort_order as SortOrder, m.is_active as IsActive, 
                       m.description as Description
                FROM sys_menus m
                INNER JOIN sys_role_menus rm ON m.id = rm.menu_id
                INNER JOIN sys_user_roles ur ON rm.role_id = ur.role_id
                INNER JOIN sys_roles r ON ur.role_id = r.id
                WHERE ur.user_id = @UserId AND m.is_active = 1 AND r.is_active = 1
                ORDER BY m.sort_order, m.id";
            
            var menus = await connection.QueryAsync<MenuDto>(sql, new { UserId = userId });
            return menus.ToList();
        }

        public async Task<bool> MenuCodeExistsAsync(string menuCode, int? excludeId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "SELECT COUNT(*) FROM sys_menus WHERE menu_code = @MenuCode";
            var parameters = new DynamicParameters();
            parameters.Add("@MenuCode", menuCode);
            
            if (excludeId.HasValue)
            {
                sql += " AND id != @ExcludeId";
                parameters.Add("@ExcludeId", excludeId.Value);
            }
            
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }
    }
}